package com.idnx.mynewsdemo.database;


import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class DBHelper extends SQLiteOpenHelper {

    private static final String DATABASE_NAME = "video_app.db";
    private static final int DATABASE_VERSION = 1;
    private static final String TABLE_VIDEOS = "videos";
    private static final String TABLE_USERS = "users";
    private static final String TABLE_TAGS = "tags";

    private static final String COLUMN_ID = "_id";
    private static final String COLUMN_TITLE = "title";
    private static final String COLUMN_DESCRIPTION = "description";
    private static final String COLUMN_VIDEO_PATH = "video_path";
    private static final String COLUMN_USER_ID = "user_id";
    private static final String COLUMN_USERNAME = "username";
    private static final String COLUMN_PASSWORD = "password";
    private static final String COLUMN_AVATAR_PATH = "avatar_path";
    private static final String COLUMN_TAG_NAME = "tag_name";
    private static final String COLUMN_VIDEO_ID = "video_id";

    public DBHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_VIDEOS_TABLE = "CREATE TABLE " + TABLE_VIDEOS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_TITLE + " TEXT,"
                + COLUMN_DESCRIPTION + " TEXT,"
                + COLUMN_VIDEO_PATH + " TEXT,"
                + COLUMN_USER_ID + " INTEGER,"
                + "FOREIGN KEY (" + COLUMN_USER_ID + ") REFERENCES " + TABLE_USERS + " (" + COLUMN_ID + "));";
        String CREATE_USERS_TABLE = "CREATE TABLE " + TABLE_USERS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_USERNAME + " TEXT,"
                + COLUMN_PASSWORD + " TEXT,"
                + COLUMN_AVATAR_PATH + " TEXT);";
        String CREATE_TAGS_TABLE = "CREATE TABLE " + TABLE_TAGS + "("
                + COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
                + COLUMN_TAG_NAME + " TEXT,"
                + COLUMN_VIDEO_ID + " INTEGER,"
                + "FOREIGN KEY (" + COLUMN_VIDEO_ID + ") REFERENCES " + TABLE_VIDEOS + " (" + COLUMN_ID + "));";
        db.execSQL(CREATE_VIDEOS_TABLE);
        db.execSQL(CREATE_USERS_TABLE);
        db.execSQL(CREATE_TAGS_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_VIDEOS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_TAGS);
        onCreate(db);
    }

    public static String getVideosTable() {
        return TABLE_VIDEOS;
    }

    public static String getUsersTable() {
        return TABLE_USERS;
    }

    public static String getTagsTable() {
        return TABLE_TAGS;
    }

    // Accessor methods for column names
    public static String getIdColumn() { return COLUMN_ID; }
    public static String getTitleColumn() { return COLUMN_TITLE; }
    public static String getDescriptionColumn() { return COLUMN_DESCRIPTION; }
    public static String getVideoPathColumn() { return COLUMN_VIDEO_PATH; }
    public static String getUserIdColumn() { return COLUMN_USER_ID; }
    public static String getUsernameColumn() { return COLUMN_USERNAME; }
    public static String getPasswordColumn() { return COLUMN_PASSWORD; }
    public static String getAvatarPathColumn() { return COLUMN_AVATAR_PATH; }
    public static String getTagNameColumn() { return COLUMN_TAG_NAME; }

    public Cursor getAllVideoPaths() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT " + COLUMN_VIDEO_PATH + " FROM " + TABLE_VIDEOS, null);
        return cursor;
    }


    @SuppressLint("Range")
    public List<String> getVideoPaths() {
        SQLiteDatabase db = this.getReadableDatabase();
        List<String> videoPaths = new ArrayList<>();
        Cursor cursor = db.rawQuery("SELECT " + COLUMN_VIDEO_PATH + " FROM " + TABLE_VIDEOS, null);
        if (cursor.moveToFirst()) {
            do {
                String videoPath = cursor.getString(cursor.getColumnIndex(COLUMN_VIDEO_PATH));
                videoPaths.add(videoPath);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return videoPaths;
    }

    @SuppressLint("Range")
    // 获取用户头像路径
    public String getAvatarPathByUserId(String userId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_USERS, new String[]{COLUMN_AVATAR_PATH}, COLUMN_ID + " = ?", new String[]{userId}, null, null, null);
        if (cursor != null && cursor.moveToFirst()) {
            String avatarPath = cursor.getString(cursor.getColumnIndex(COLUMN_AVATAR_PATH));
            cursor.close();
            return avatarPath;
        }
        return null;
    }

    @SuppressLint("Range")
    // 获取视频描述
    public String getDescriptionByVideoId(String videoId) {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.query(TABLE_VIDEOS, new String[]{COLUMN_DESCRIPTION}, COLUMN_ID + " = ?", new String[]{videoId}, null, null, null);
        if (cursor != null && cursor.moveToFirst()) {
            String description = cursor.getString(cursor.getColumnIndex(COLUMN_DESCRIPTION));
            cursor.close();
            return description;
        }
        return null;
    }

    @SuppressLint("Range")
    // 获取视频的所有标签
    public List<String> getTagsByVideoId(String videoId) {
        SQLiteDatabase db = this.getReadableDatabase();
        List<String> tags = new ArrayList<>();
        Cursor cursor = db.rawQuery("SELECT " + COLUMN_TAG_NAME + " FROM " + TABLE_TAGS + " WHERE " + COLUMN_VIDEO_ID + " = ?", new String[]{videoId});
        if (cursor.moveToFirst()) {
            do {
                String tagName = cursor.getString(cursor.getColumnIndex(COLUMN_TAG_NAME));
                tags.add(tagName);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return tags;
    }


}
